package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;


public class PartSaleDB implements PartSaleDBIF {
	
	private  Connection con;
    /** Creates a new instance of DBProduct */
    public PartSaleDB() {
      con = DbConnection1.getInstance().getDBcon();
    }

	public ArrayList<PartSale> getAllPartSales(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	public PartSale findPartSale(int psID, boolean retrieveAssociation) {
		String wClause = "  psID = '" + psID + "'";
        return singleWhere(wClause, retrieveAssociation);
	}

	public int createPartSale(PartSale ps, Sale s) throws Exception {
		//call to get the next ID number
        int nextID = GetMax.getMaxId("Select max(psid) from partsale");
        nextID = nextID + 1;
        System.out.println("next psID = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO partsale(psid, psamount, psprice, psprodid, pssaleid, pricetype)  VALUES('"+
	  		nextID + "','" +
			ps.getAmount()  + "','"  +
		    ps.getPrice() + "','"  +
		    ps.getProduct().getProductId() + "','"  +
		    s.getSaleId() + "','"  +
		    ps.getPriceType() + "')";
            
       System.out.println("insert : " + query);
      try{ // insert new partsale
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("PartSale ikke oprettet");
          throw new Exception ("PartSale is not inserted correct");
       }
       return(rc);
	}

	@Override
	public int updatePartSale(PartSale ps) {
		// TODO Auto-generated method stub
		return 0;
	}

	public int deletePartSale(int psID) {
		int rc=-1;
		  
	  	String query="DELETE FROM partsale WHERE psID = '" +
	  			psID + "'";
                System.out.println(query);
	  	try{ // delete from partsale
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in partsale db: "+ex);
   	        }
		return(rc);
	}
	
	private PartSale singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        PartSale sObj = new PartSale();
        String query =  buildQuery(wClause);
        System.out.println("DbSale -singleWhere " + query); 
        try{ // read from Sale
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

	   if( results.next() ){
	       sObj = buildPartSale(results);
               //missing the test on retriveassociation
               
           }//end if
	   stmt.close();
	   if(retrieveAssociation)
       {   //The Product is to be build as well
		   int productId = sObj.getProduct().getProductId();
           ProductDB dbProduct= new ProductDB();
           Product p = dbProduct.findProduct(productId, false);
           sObj.setProduct(p);
           System.out.println("Product is seleceted");
           
	}//slut try	
        else{ //no order was found
            sObj = null;
        }
        }
	catch(Exception e){
	   System.out.println("Query exception - select PartSale : "+e);
	   e.printStackTrace();
        }
	return sObj;
    }
	
	 //miscWhere is used when more than one Product is selected and build
    private ArrayList<PartSale> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<PartSale> list = new ArrayList<PartSale>();
		
		String query =  buildQuery(wClause);
                System.out.println("PartSaleDB " + query); 
  		try{ // read from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 	
			while( results.next() ){
				PartSale sObj = new PartSale();
			 sObj = buildPartSale(results);	
                         list.add(sObj);
                         //missing test on retriveAssociation
			}//end while
			stmt.close();
			if(retrieveAssociation)
            {   //The Product is to be build as well
                for(PartSale sObj : list){
         		   int prodId = sObj.getProduct().getProductId();
                   ProductDB dbProduct= new ProductDB();
                   Product p = dbProduct.findProduct(prodId, false);
                   sObj.setProduct(p);
                   System.out.println("Product is seleceted");
                }
            }//end if   
		
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select partsale : "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    private PartSale buildPartSale(ResultSet results)
    {
    	PartSale psObj = new PartSale();
    	Product pObj = new Product();
       
         try{
               psObj.setId(results.getInt("psid"));
               psObj.setAmount(results.getInt("psamount"));
               psObj.setPrice(results.getDouble("psprice"));
               pObj.setProductId(results.getInt("productId"));
               psObj.setProduct(pObj);
               psObj.setPriceType(results.getString("PriceType"));
          }
         catch(Exception e)
         {
             System.out.println("building PartSale object");
         }
        
        return psObj;
        
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT psid, psamount, psprice, psprodid, PriceType FROM partsale, product where psprodid=productid "; 
            
           

		if (wClause.length()>0)
			query = query + "  and " + wClause;
			
		return query;
	}

}
